package org.gaojianqiao.electronichandbook.database;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.gaojianqiao.electronichandbook.createtable.OperateGuideTab;

import zyg.login.DataBas;

public class OperateOperateGuideTab
{
	public ArrayList<OperateGuideTab> OperateGuidelist(String bookName)
			throws SQLException, UnsupportedEncodingException
	{

		// TODO Auto-generated method stub

		String sql = "select * from " + bookName + " order by adr";
		DataBas publicDataBase = new DataBas();
		publicDataBase.connectDB("data3");
		ResultSet rs = publicDataBase.stmt.executeQuery(sql);
		ArrayList<OperateGuideTab> OperateGuidelist = new ArrayList<OperateGuideTab>();
		while (rs.next())
		{
			OperateGuideTab OperateGuide = new OperateGuideTab();
			OperateGuide.setadr(rs.getInt("adr"));
			OperateGuide.setquestion(rs.getString("question"));
			OperateGuide.setyes_point(rs.getInt("yes_point"));
			OperateGuide.setno_point(rs.getInt("no_point"));
			OperateGuide.setreferdata(rs.getString("referdata"));
			OperateGuidelist.add(OperateGuide);
			// System.out.println(""+rs.getString("weapon_id")+rs.getString("weapon_title")+rs.getString("OperateGuide"));
		}
		publicDataBase.closeDB();

		return OperateGuidelist;
	}

	public void insert(ArrayList<OperateGuideTab> str) throws SQLException,
			ClassNotFoundException
	{
		String sql = "insert into OperateGuideTab values (?,?,?,?)";
		DataBas publicDataBase = new DataBas();
		Connection conn = publicDataBase.connectDB("public");
		PreparedStatement ps = conn.prepareStatement(sql);
		int i = 0;
		do
		{
			ps.setInt(1, str.get(i).getadr());
			ps.setString(2, str.get(i).getquestion());
			ps.setInt(3, str.get(i).getyes_point());
			ps.setInt(4, str.get(i).getno_point());
			ps.setString(5, str.get(i).getreferdata());
			ps.executeUpdate();
			++i;
		} while (i < str.size());
		ps.close();
		conn.close();

	}

	public void createtable( ) throws SQLException, ClassNotFoundException
	{

		String sql2 = "drop table if exists OperateOperateGuideTab";
		String sql3 = "create table OperateOperateGuideTab (adr integer,question text,yes_point integer,no_point integer,photoidArray text)";
		DataBas publicDataBase = new DataBas();
		Connection conn = publicDataBase.connectDB("data3");
		Statement stat = conn.createStatement();
		conn.setAutoCommit(false);
		stat.executeUpdate(sql2);
		stat.executeUpdate(sql3);
		conn.commit();
		conn.close();
	}
}
